package com.oocl.consumer.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.oocl.consumer.dao.BookDao;
import com.oocl.consumer.pojo.Book;
import com.oocl.consumer.util.DBHelper;


public class BookDaoImpl implements BookDao {

	public int addBook(Book book) {
		String sql = "insert into books(id, name, pub_date, author, price, is_new, publisher) values(seq01.nextval,?,?,?,?,?,?)";
		Connection conn = DBHelper.getConnectionWithDs();
		PreparedStatement pst = null;
		try {
			pst = conn.prepareStatement(sql);
			DBHelper.setSqlValues(pst, book.getName(), new Date(book.getPubDate().getTime()), book.getAuthor(), 
					book.getPrice(), book.getIsNew(), book.getPublisher());
			return pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.close(pst, null, conn);
		}
		return -1;
	}
	
	public int addBatchBook(List<Book> books, int limit) {
		String sql = "insert into books(id, name, pub_date, author, price, is_new, publisher) values(seq01.nextval,?,?,?,?,?,?)";
		Connection conn = DBHelper.getConnectionWithDs();
		PreparedStatement pst = null;
		Book book = null;
		try {
			//设置为手动提交
			conn.setAutoCommit(false);
			pst = conn.prepareStatement(sql);
			int len = books.size();
            for(int i=0; i<len; i++) {
            	book = books.get(i);
            	DBHelper.setSqlValues(pst, book.getName(), new Date(book.getPubDate().getTime()), book.getAuthor(), 
    					book.getPrice(), book.getIsNew(), book.getPublisher());
            	//优化插入第二步, 插入代码打包，等一定量后再一起插入。
                pst.addBatch();  
                //每limit次提交一次  
                if((i!=0 && i % limit == 0) || i== len-1 ){
                    pst.executeBatch();   
                    //优化插入第三步。提交，批量插入数据库中。
                    conn.commit();  
                    //提交后，Batch清空。
                    pst.clearBatch();  
                }
            }
		} catch (SQLException e) {
			e.printStackTrace();
		}  finally {
			DBHelper.close(pst, null, conn);
		}
		return books.size();
	}

	public int deleteBook(Integer id) {
		String sql = "delete books where id = ? ";
		Connection conn = DBHelper.getConnectionWithDs();
		PreparedStatement pst = null;
		try {
			pst = conn.prepareStatement(sql);
			DBHelper.setSqlValues(pst, id);
			return pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.close(pst, null, conn);
		}
		return -1;
	}

	public int updateBook(Book book) {
		String sql = "update books set name=?, pub_date=?, author=?, price=?, is_new=?, publisher=? where id = ? ";
		Connection conn = DBHelper.getConnectionWithDs();
		PreparedStatement pst = null;
		try {
			pst = conn.prepareStatement(sql);
			DBHelper.setSqlValues(pst, book.getName(), new Date(book.getPubDate().getTime()), book.getAuthor(), 
					book.getPrice(), book.getIsNew(), book.getPublisher(), book.getId());
			return pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.close(pst, null, conn);
		}
		return -1;
	}

	public List<Book> queryAllBook() {
		Connection conn = DBHelper.getConnectionWithDs();
		List<Book> list = new ArrayList<Book>();
		Book book = null;
		String sql = "select * from books";
		PreparedStatement pst = null;
		ResultSet rst = null;
		try {
			pst =  conn.prepareStatement(sql);
			rst = pst.executeQuery();
		
			while(rst.next()){
				book = new Book();
				book.setId(rst.getInt(1));
				book.setName(rst.getString(2));
				book.setPubDate(rst.getDate(3));
				book.setAuthor(rst.getString(4));
				book.setPrice(rst.getDouble(5));
				book.setIsNew(rst.getString(6));
				book.setPublisher(rst.getString(7));
				list.add(book);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.close(pst, rst, conn);
		}
		return list;
	}

	public Book queryBookById(Integer id) {
		Connection conn = DBHelper.getConnectionWithDs();
		Book book = null;
		String sql = "select * from books where id = ?";
		PreparedStatement pst = null;
		ResultSet rst = null;
		try {
			pst =  conn.prepareStatement(sql);
			pst.setInt(1, id);
			rst = pst.executeQuery();
		
			while(rst.next()){
				book = new Book();
				book.setId(rst.getInt(1));
				book.setName(rst.getString(2));
				book.setPubDate(rst.getDate(3));
				book.setAuthor(rst.getString(4));
				book.setPrice(rst.getDouble(5));
				book.setIsNew(rst.getString(6));
				book.setPublisher(rst.getString(7));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.close(pst, rst, conn);
		}
		return book;
	}

}
